--POUR CONTROLE


define erp='erp_mli2.';
define outils='outils_mli2.';



/*CALL uvse_ExistsOrCreate('STO_DEPOT');   */

/*
select * from (
SELECT &outils.stragg(source) source,depot_cod, enreg_id, marque_cod, fournisseur_cod, client_cod, magasinier_cod, adresse_id, unfab_cod, prfsto_cod, case_neutre_id, case_attente_id, case_prepa_id, enreg_s, depot_lib, depot_lib2, bloque_ok, emplacement_ok, clotureinterdite_ok, affectautoart_ok, calculpmd_ok, prfserie_cod, prflot_cod, prfinvent_cod, depot_empl_cod, prfempl_cod, prfidentstk_cod, prfrupt_cod
FROM (
  SELECT 'uv' source ,g.* FROM se_STO_DEPOT g
  UNION ALL
  SELECT 'se' source ,g.* FROM &erp.STO_DEPOT g
  )
GROUP BY depot_cod, enreg_id, marque_cod, fournisseur_cod, client_cod, magasinier_cod, adresse_id, unfab_cod, prfsto_cod, case_neutre_id, case_attente_id, case_prepa_id, enreg_s, depot_lib, depot_lib2, bloque_ok, emplacement_ok, clotureinterdite_ok, affectautoart_ok, calculpmd_ok, prfserie_cod, prflot_cod, prfinvent_cod, depot_empl_cod, prfempl_cod, prfidentstk_cod, prfrupt_cod
HAVING Count(1)!=2) r
ORDER BY depot_cod*/

--SELECT * FROM uv_plieu_stock
DELETE FROM  se_STO_DEPOT ;
--SELECT Max(ID) FROM uv_pfournisseurs WHERE depot IS NOT NULL AND depot !='01' ORDER BY depot

INSERT INTO se_STO_DEPOT
 (SELECT ID depot_cod,
         0 enreg_id,
         NULL marque_cod,
         NULL fournisseur_cod,
         NULL client_cod,
         NULL magasinier_cod,
         0 adresse_id,
         Nvl((SELECT Max(ID) FROM uv_pfournisseurs f WHERE f.depot =fuv.id AND f.depot !='01'),'9099') unfab_cod,
         null prfsto_cod,
         null case_neutre_id,
         null case_attente_id,
         null case_prepa_id,
         'N' enreg_s,
         LIBELLE depot_lib,
         NULL depot_lib2,
         'N' bloque_ok,
         'O' emplacement_ok,
         'N' clotureinterdite_ok,
         'N' affectautoart_ok,
         'O' calculpmd_ok,
         NULL prfserie_cod,
         NULL prflot_cod,
         NULL prfinvent_cod,
         NULL depot_empl_cod,
         NULL prfempl_cod,
         NULL prfidentstk_cod,
         NULL prfrupt_cod
 FROM uv_plieu_stock fuv) ;

 UPDATE se_STO_DEPOT tf
 SET enreg_id = (select enreg_id FROM &erp.STO_DEPOT f WHERE f.depot_cod=tf.depot_cod )
 WHERE EXISTS (SELECT 1 FROM &erp.STO_DEPOT f WHERE f.depot_cod=tf.depot_cod);

 UPDATE se_STO_DEPOT SET enreg_id=&outils.enreg_id.NEXTVAL WHERE enreg_id IS NULL OR enreg_id =0;


 UPDATE se_STO_DEPOT tf
 SET adresse_id = (select adresse_id FROM &erp.STO_DEPOT f WHERE f.depot_cod=tf.depot_cod )
 WHERE EXISTS (SELECT 1 FROM &erp.STO_DEPOT f WHERE f.depot_cod=tf.depot_cod);

 UPDATE se_STO_DEPOT SET adresse_id=&outils.gen_adresse.NEXTVAL WHERE adresse_id IS NULL OR adresse_id =0;




/*CALL uvse_ExistsOrCreate('GEN_ADRESSE'); */

/*SELECT &outils.stragg(source) source,adresse_id, strucjur_cod, traduction_cod, expedzone_cod, enreg_id, pays_cod, departement_cod, region_cod, prefnom_cod, nom, nom2, adr1, adr2, adr3, bp, cpostal_cod, ville_cod, tel1, tel2, fax1, fax2, telmobile1, telmobile2, email, siteweb, contact_nom, tvacee_no, siret_no, ape_no, gln_no, cnuf_no, rc_no, villerc_lib, capital, logoachat_lib, creatsoc_dat, envoi_typ, villeinsee_cod, ediadr_no
FROM (
  SELECT 'uv' source ,g.* FROM se_gen_adresse g
  UNION ALL
  SELECT 'se' source ,g.* FROM &erp.gen_adresse g WHERE EXISTS (SELECT * FROM se_sto_depot a WHERE a.adresse_id=g.adresse_id)
  )
GROUP BY adresse_id, strucjur_cod, traduction_cod, expedzone_cod, enreg_id, pays_cod, departement_cod, region_cod, prefnom_cod, nom, nom2, adr1, adr2, adr3, bp, cpostal_cod, ville_cod, tel1, tel2, fax1, fax2, telmobile1, telmobile2, email, siteweb, contact_nom, tvacee_no, siret_no, ape_no, gln_no, cnuf_no, rc_no, villerc_lib, capital, logoachat_lib, creatsoc_dat, envoi_typ, villeinsee_cod, ediadr_no
HAVING Count(1)!=2
ORDER BY adresse_id*/

/*select * from se_gen_adresse g where not exists (select 1 from &erp.par_departement d where d.pays_cod=g.pays_cod and d.departement_cod=g.departement_cod)*/

DELETE FROM  se_gen_adresse ;

INSERT INTO se_gen_adresse
 (SELECT (SELECT adresse_id FROM se_STO_DEPOT s WHERE s.depot_cod=fuv.ID) adresse_id,
    NULL strucjur_cod,
    null traduction_cod,
    NULL expedzone_cod,
    0 enreg_id,
    null pays_cod,
    dep departement_cod,
    NULL region_cod,
    NULL prefnom_cod,
    Trim(libelle) nom,
    NULL nom2,
    null adr1,
    null adr2,
    NULL adr3,
    NULL bp,
    NULL cpostal_cod,
    NULL ville_cod,
    NULL tel1,
    NULL tel2,
    NULL fax1,
    NULL fax2,
    null telmobile1,
    NULL telmobile2,
    NULL email,
    NULL siteweb,
    NULL contact_nom,
    NULL tvacee_no,
    NULL siret_no,
    NULL ape_no,
    NULL gln_no,
    NULL cnuf_no,
    NULL rc_no,
    NULL villerc_lib,
    NULL capital,
    NULL logoachat_lib,
    NULL creatsoc_dat,
    NULL envoi_typ,
    NULL villeinsee_cod,
    NULL ediadr_no
    FROM uv_plieu_stock fuv
   ) ;

 UPDATE se_gen_adresse tf
 SET enreg_id = (select enreg_id FROM &erp.gen_adresse f WHERE f.adresse_id=tf.adresse_id )
 WHERE EXISTS (SELECT 1 FROM &erp.gen_adresse f WHERE f.adresse_id=tf.adresse_id);

 UPDATE se_gen_adresse SET enreg_id=&outils.enreg_id.NEXTVAL WHERE enreg_id IS NULL OR enreg_id =0;

 CALL uvse_synchroverserp('adresse_id','GEN_ADRESSE','O') ;
 CALL uvse_synchroverserp('depot_cod','sto_depot','O') ;

 update &erp.sto_depot SET EMPLACEMENT_OK ='O' WHERE depot_cod IN ('01','05','06','07');
 update &erp.sto_depot SET EMPLACEMENT_OK ='N' WHERE depot_cod NOT IN ('01','05','06','07');


 commit;